package com.cennavi.bigdata.baotou.scripts;

import com.alibaba.fastjson.JSONObject;
import com.mongodb.BasicDBObject;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
import org.apache.commons.dbcp.BasicDataSource;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by cennavi on 2017/11/27.
 */
public class InsertMongoKakouFlowHour extends ActualTimeRequest {

    private DBCollection corresKakouFlowHour = null;
    private static final org.slf4j.Logger LOGGER = LoggerFactory.getLogger(InsertMongoKakouFlowHour.class);
    public Connection getConnection() {
        Connection connection = null;
        try {
            BasicDataSource basicDataSource = new BasicDataSource();
            basicDataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
            basicDataSource.setUrl("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=15.32.12.203)(PORT=1521))(ADDRESS = (PROTOCOL = TCP)(HOST =15.32.12.203 )(PORT = 1521))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=jczhdb)))");
            basicDataSource.setUsername("btjczh");
            basicDataSource.setPassword("btjczh1314");
            connection = basicDataSource.getConnection();
            System.out.println("数据库连接成功");

        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }


    public List<String> getDateType(String dateType){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<String> list = new ArrayList<>();
        connection = getConnection();
        String sql;
        if (dateType.equals("work")||dateType.equals("nwork")){
            sql = "select tjsd from N_WORKDAYS";
        }else {
            sql = "select tjsd from N_WORKDAYS where CATEGORYNAME='holi'";
        }
        try {
            statement = connection .prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                String tjsd = resultSet.getString("tjsd");
                list.add(tjsd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

//    @Test
    public void insertMongoKakouFlowHourWork() {
        String format1 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoKakouFlowHourWork开始时间："+ format1);
        corresKakouFlowHour = getMongoCollection("kakouFlowHour");
//        corresKakouFlowHour.drop();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.MONTH,-1);
        String timeTemp = new SimpleDateFormat("yyyyMM").format(calendar.getTime());
        String sql = "select tjsd,xjll,xjbh FROM ctl_xjll_hour_history_"+ timeTemp +" order by xjbh ";

        List<String> tjsdList = getDateType("work");
        Set<String> xjbhSet = new HashSet<>();
        Set<String> tjsdSet = new HashSet<>();
        JSONObject xjbhTjsdJson = new JSONObject();
        connection = getConnection();
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String tjsd = resultSet.getString("tjsd");
                int xjll = resultSet.getInt("xjll");
                String xjbh = resultSet.getString("xjbh");
                if (!tjsdList.contains(tjsd.split(" ")[0])) {
                    if (xjbhTjsdJson.containsKey(xjbh)) {
                        JSONObject reaTjsdSplit = xjbhTjsdJson.getJSONObject(xjbh);
                        if (reaTjsdSplit.containsKey(tjsd.split(" ")[1])) {
                            int realXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1]);
                            int maxXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "max");
                            int count = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "count");
                            realXjll += xjll;
                            count++;
                            reaTjsdSplit.put(tjsd.split(" ")[1], realXjll / 2);
                            reaTjsdSplit.put(tjsd.split(" ")[1]+"count", count);
                            if (maxXjll > xjll) {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", maxXjll);
                            } else {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            }
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        } else {
                            reaTjsdSplit.put(tjsd.split(" ")[1], xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "count", 1);
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        }
                    } else {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put(tjsd.split(" ")[1], xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "max", xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "count", 1);
                        xjbhTjsdJson.put(xjbh, jsonObject);
                    }
                }
                tjsdSet.add(tjsd.split(" ")[1]);
                xjbhSet.add(xjbh);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {resultSet.close();}
                if (statement != null) {statement.close();}
                if (connection != null) {connection.close();}
            } catch (Exception e) {
                e.getStackTrace();
            } finally {
                System.out.println("连接已关闭");
            }
        }
        List<DBObject> mongoList = new ArrayList<>();
        Iterator<String> iterator = xjbhSet.iterator();
        while (iterator.hasNext()) {
            String next = iterator.next();
            if (xjbhTjsdJson.containsKey(next)){
                JSONObject jsonObject = xjbhTjsdJson.getJSONObject(next);
                Iterator<String> iterator1 = tjsdSet.iterator();
                while (iterator1.hasNext()) {
                    String next1 = iterator1.next();
                    if (jsonObject.containsKey(next1)) {
                        int avgxjll = jsonObject.getInteger(next1);
                        int maxxjll = jsonObject.getInteger(next1 + "max");
                        int count = jsonObject.getInteger(next1 + "count");
                        DBObject json = new BasicDBObject();
                        json.put("kkbh", next);
                        json.put("time", next1);
                        json.put("maxKkll", maxxjll);
                        json.put("avgKkll", avgxjll);
                        json.put("date", timeTemp);
//                        json.put("date", "201710");
                        json.put("dateType", "work");
                        json.put("count", count);
                        mongoList.add(json);
                    }
                }
            }
        }
        if (mongoList.size() > 0) {
            corresKakouFlowHour.insert(mongoList);
        }
        String format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoKakouFlowHourWork完成时间：" + format);
        insertMongoKakouFlowHourNWork();
        insertMongoKakouFlowHourHoli();
    }

    public void insertMongoKakouFlowHourNWork() {
        String format1 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoKakouFlowHourNWork开始时间：" + format1);
        corresKakouFlowHour = getMongoCollection("kakouFlowHour");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.MONTH,-1);
        String timeTemp = new SimpleDateFormat("yyyyMM").format(calendar.getTime());
        String sql = "select tjsd,xjll,xjbh FROM ctl_xjll_hour_history_"+ timeTemp +" order by xjbh ";

        List<String> tjsdList = getDateType("nwork");
        Set<String> xjbhSet = new HashSet<>();
        Set<String> tjsdSet = new HashSet<>();
        JSONObject xjbhTjsdJson = new JSONObject();
        connection = getConnection();
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String tjsd = resultSet.getString("tjsd");
                int xjll = resultSet.getInt("xjll");
                String xjbh = resultSet.getString("xjbh");
                if (tjsdList.contains(tjsd.split(" ")[0])) {
                    if (xjbhTjsdJson.containsKey(xjbh)) {
                        JSONObject reaTjsdSplit = xjbhTjsdJson.getJSONObject(xjbh);
                        if (reaTjsdSplit.containsKey(tjsd.split(" ")[1])) {
                            int realXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1]);
                            int maxXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "max");
                            int count = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "count");
                            realXjll += xjll;
                            count++;
                            reaTjsdSplit.put(tjsd.split(" ")[1], realXjll / 2);
                            reaTjsdSplit.put(tjsd.split(" ")[1]+"count", count);
                            if (maxXjll > xjll) {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", maxXjll);
                            } else {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            }
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        } else {
                            reaTjsdSplit.put(tjsd.split(" ")[1], xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "count", 1);
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        }
                    } else {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put(tjsd.split(" ")[1], xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "max", xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "count", 1);
                        xjbhTjsdJson.put(xjbh, jsonObject);
                    }
                }
                tjsdSet.add(tjsd.split(" ")[1]);
                xjbhSet.add(xjbh);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {resultSet.close();}
                if (statement != null) {statement.close();}
                if (connection != null) {connection.close();}
            } catch (Exception e) {
                e.getStackTrace();
            } finally {
                System.out.println("连接已关闭");
            }
        }
        List<DBObject> mongoList = new ArrayList<>();
        Iterator<String> iterator = xjbhSet.iterator();
        while (iterator.hasNext()) {
            String next = iterator.next();
            if (xjbhTjsdJson.containsKey(next)){
                JSONObject jsonObject = xjbhTjsdJson.getJSONObject(next);
                Iterator<String> iterator1 = tjsdSet.iterator();
                while (iterator1.hasNext()) {
                    String next1 = iterator1.next();
                    if (jsonObject.containsKey(next1)) {
                        int avgxjll = jsonObject.getInteger(next1);
                        int maxxjll = jsonObject.getInteger(next1 + "max");
                        int count = jsonObject.getInteger(next1 + "count");
                        DBObject json = new BasicDBObject();
                        json.put("kkbh", next);
                        json.put("time", next1);
                        json.put("maxKkll", maxxjll);
                        json.put("avgKkll", avgxjll);
                        json.put("date", timeTemp);
//                        json.put("date", "201710");
                        json.put("dateType", "nwork");
                        json.put("count", count);
                        mongoList.add(json);
                    }
                }
            }
        }
        if (mongoList.size() > 0) {
            corresKakouFlowHour.insert(mongoList);
        }
        String format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoKakouFlowHourNWork完成时间：" + format);
    }



    public void insertMongoKakouFlowHourHoli() {
        String format3 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoKakouFlowHourHoli完成时间：" + format3);

        corresKakouFlowHour = getMongoCollection("kakouFlowHour");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.MONTH,-1);
        String timeTemp = new SimpleDateFormat("yyyyMM").format(calendar.getTime());
        String sql = "select tjsd,xjll,xjbh FROM ctl_xjll_hour_history_"+ timeTemp +" order by xjbh ";

        List<String> tjsdList = getDateType("holi");
        Set<String> xjbhSet = new HashSet<>();
        Set<String> tjsdSet = new HashSet<>();
        JSONObject xjbhTjsdJson = new JSONObject();
        connection = getConnection();
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String tjsd = resultSet.getString("tjsd");
                int xjll = resultSet.getInt("xjll");
                String xjbh = resultSet.getString("xjbh");
                if (tjsdList.contains(tjsd.split(" ")[0])) {
                    if (xjbhTjsdJson.containsKey(xjbh)) {
                        JSONObject reaTjsdSplit = xjbhTjsdJson.getJSONObject(xjbh);
                        if (reaTjsdSplit.containsKey(tjsd.split(" ")[1])) {
                            int realXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1]);
                            int maxXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "max");
                            int count = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "count");
                            realXjll += xjll;
                            count++;
                            reaTjsdSplit.put(tjsd.split(" ")[1], realXjll / 2);
                            reaTjsdSplit.put(tjsd.split(" ")[1]+"count", count);
                            if (maxXjll > xjll) {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", maxXjll);
                            } else {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            }
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        } else {
                            reaTjsdSplit.put(tjsd.split(" ")[1], xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "count", 1);
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        }
                    } else {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put(tjsd.split(" ")[1], xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "max", xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "count", 1);
                        xjbhTjsdJson.put(xjbh, jsonObject);
                    }
                }
                tjsdSet.add(tjsd.split(" ")[1]);
                xjbhSet.add(xjbh);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {resultSet.close();}
                if (statement != null) {statement.close();}
                if (connection != null) {connection.close();}
            } catch (Exception e) {
                e.getStackTrace();
            } finally {
                System.out.println("连接已关闭");
            }
        }
        List<DBObject> mongoList = new ArrayList<>();
        Iterator<String> iterator = xjbhSet.iterator();
        while (iterator.hasNext()) {
            String next = iterator.next();
            if (xjbhTjsdJson.containsKey(next)){
                JSONObject jsonObject = xjbhTjsdJson.getJSONObject(next);
                Iterator<String> iterator1 = tjsdSet.iterator();
                while (iterator1.hasNext()) {
                    String next1 = iterator1.next();
                    if (jsonObject.containsKey(next1)) {
                        int avgxjll = jsonObject.getInteger(next1);
                        int maxxjll = jsonObject.getInteger(next1 + "max");
                        int count = jsonObject.getInteger(next1 + "count");
                        DBObject json = new BasicDBObject();
                        json.put("kkbh", next);
                        json.put("time", next1);
                        json.put("maxKkll", maxxjll);
                        json.put("avgKkll", avgxjll);
                        json.put("count", count);
                        json.put("date", timeTemp);
//                        json.put("date", "201710");
                        json.put("dateType", "holi");
                        mongoList.add(json);
                    }
                }
            }
        }
        if (mongoList.size() > 0) {
            corresKakouFlowHour.insert(mongoList);
        }
        String format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoKakouFlowHourHoli完成时间：" + format);
        closeMongo();
    }
}
